/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tamnhat.report.controller;

import com.tamnhat.lazyLoading.LazyAbstractDataModel;
import com.tamnhat.report.entity.BaocaoPro;
import com.tamnhat.report.entity.Sanpham;
import com.tamnhat.report.entity.Doitac;
import com.telsoft.applet.util.DateUtil;
import com.telsoft.applet.util.StringUtil;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.faces.application.FacesMessage;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.SessionScoped;
import javax.faces.bean.ViewScoped;
import javax.faces.context.FacesContext;
import javax.faces.model.DataModel;
import org.primefaces.model.LazyDataModel;
import telsoft.admin.util.AppServer;
import telsoft.cms_admin.login.LoginBean;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */
@ManagedBean
@ViewScoped
public class BaocaoSLDVController implements Serializable {

    private Date tuNgay;
    private Date denNgay;
    private ArrayList<Doitac> listDoitac = new ArrayList<Doitac>();
    private ArrayList<Sanpham> listDichvu = new ArrayList<Sanpham>();
    private List<String> chitietTheoNgay;
    private String doitac;
    private String dichvu;
    private List<BaocaoPro> items = new ArrayList<BaocaoPro>();
    private LazyDataModel<BaocaoPro> lazyDataModel;

    /**
     * Creates a new instance of BaocaoSLDVController
     */
    public BaocaoSLDVController() {
        Long i = Long.parseLong(FacesContext.getCurrentInstance().getExternalContext().getSessionMap().get(LoginBean.USER_ID).toString());
        try {
            listDoitac = BaocaoPro.listDoitac(i);
            listDichvu = BaocaoPro.listSanpham(i);
        } catch (Exception e) {
            e.printStackTrace();
            FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Error: " + e.getMessage()));
        }
    }

    public LazyDataModel<BaocaoPro> getLazyDataModel() {
        return lazyDataModel;
    }

    public void setLazyDataModel(LazyDataModel<BaocaoPro> lazyDataModel) {
        this.lazyDataModel = lazyDataModel;
    }

    public List<BaocaoPro> getItems() {
        return items;
    }

    public void setItems(List<BaocaoPro> items) {
        this.items = items;
    }

    public String getDoitac() {
        return doitac;
    }

    public void setDoitac(String doitac) {
        this.doitac = doitac;
    }

    public String getDichvu() {
        return dichvu;
    }

    public void setDichvu(String dichvu) {
        this.dichvu = dichvu;
    }

    public Date getTuNgay() {
        return tuNgay;
    }

    public void setTuNgay(Date tuNgay) {
        this.tuNgay = tuNgay;
    }

    public Date getDenNgay() {
        return denNgay;
    }

    public void setDenNgay(Date denNgay) {
        this.denNgay = denNgay;
    }

    public ArrayList<Doitac> getListDoitac() {        
        return listDoitac;
    }

    public void setListDoitac(ArrayList<Doitac> listDoitac) {
        this.listDoitac = listDoitac;
    }

    public ArrayList<Sanpham> getListDichvu() {        
        return listDichvu;
    }

    public void setListDichvu(ArrayList<Sanpham> listDichvu) {
        this.listDichvu = listDichvu;
    }

    public List<String> getChitietTheoNgay() {
        return chitietTheoNgay;
    }

    public void setChitietTheoNgay(List<String> chitietTheoNgay) {
        this.chitietTheoNgay = chitietTheoNgay;
    }

    public String check() {
        return "";
    }
    // lay ve cau query theo condition

    public String getSQL(String condition, int chitiet,String dichvu,String doitac) {
        //query theo dieu kien ko theo ngay
        String SQL1 = " select sdp_products.product_name Dich_Vu,COUNT(sdp_users.created_time) SL_DK,COUNT(sdp_users.canceled_time) SL_HUY "
                + " from sdp_users,sdp_products,partners "
                + " where sdp_users.product_id = sdp_products.product_id "
                + " and ( sdp_users.product_id = " + dichvu + " or " + dichvu + " is null ) "
                + " and sdp_users.partner_id = partners.partner_id "
                + " and sdp_users.partner_id = " + doitac + " "
                + condition
                + " group by sdp_products.product_name";
        String SQL2 = "select  to_char(ngay,'dd-mm-yyyy'),dich_vu, sl_dk, sl_huy "
                + " from ("
                + " select TRUNC(sdp_users.created_time,'dd') Ngay,sdp_products.product_name Dich_Vu,COUNT(sdp_users.created_time) SL_DK,COUNT(sdp_users.canceled_time) SL_HUY "
                + " from sdp_users,sdp_products,partners"
                + " where sdp_users.product_id = sdp_products.product_id "
                + " and (sdp_users.product_id = " + dichvu + " or " + dichvu + " is null) "
                + " and sdp_users.partner_id = partners.partner_id "
                + " and sdp_users.partner_id = " + doitac + " "
                + condition
                + " group by TRUNC(sdp_users.created_time,'dd') , sdp_products.product_name "
                + " order by TRUNC(sdp_users.created_time,'dd'),sdp_products.product_name )";
//                + " order by TRUNC(sdp_users.created_time,'dd'),sdp_products.product_name";
        if (chitiet == 1) {
            return SQL1;
        }
        return SQL2;
    }
    //kiem tra input nhap vao

    public String checkInput() {
        String format = "dd-MM-yyyy";
        if (denNgay != null && tuNgay != null) {
            if (denNgay.compareTo(tuNgay) < 0) {
                FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Từ ngày và đến ngày nhập không hợp lệ!"));
                return "";
            }
        }        
        String dichvuTmp = "";
        if(dichvu.equalsIgnoreCase("")){
            dichvuTmp = null;
        }
        String condition1 = "";
        String condition2 = "";
        if (chitietTheoNgay.size() == 0) {
            if (tuNgay != null) condition1 = " and sdp_users.created_time >= TO_DATE('" + StringUtil.format(tuNgay, format) + "','dd-mm-yyyy') ";
            if (denNgay != null) condition2 = " and sdp_users.created_time < TO_DATE('" + StringUtil.format(denNgay, format) + "','dd-mm-yyyy') + 1 ";
            return getSQL(condition1 + condition2, 1,dichvuTmp,doitac);
        } else {                
            if (tuNgay != null) condition1 = " and sdp_users.created_time >= TO_DATE('" + StringUtil.format(tuNgay, format) + "','dd-mm-yyyy') ";
            if (denNgay != null) condition2 = " and sdp_users.created_time < TO_DATE('" + StringUtil.format(denNgay, format) + "','dd-mm-yyyy') + 1 ";                
            return getSQL(condition1 + condition2, 2,dichvuTmp,doitac);
        }        
    }
    
    //tim danh sach theo dieu kien
    public ArrayList<BaocaoPro> searchByCondition() {
        ArrayList<BaocaoPro> list = new ArrayList<BaocaoPro>();
        Connection cn = null;
        Statement st = null;
        ResultSet rs = null;
        String strSQL = "";
        try {            
            strSQL = checkInput();           
            if (strSQL != "") {
                cn = AppServer.getConnection();

                st = cn.createStatement();
                rs = st.executeQuery(strSQL);
                if (chitietTheoNgay.size() == 0) {
                    String from = "";
                    String to = "";
                    if (tuNgay == null) {
                        from = "-";
                    } else {
                        from = StringUtil.format(tuNgay, "dd-MM-yyyy");
                    }
                    if (denNgay == null) {
                        to = "-";
                    } else {
                        to = StringUtil.format(denNgay, "dd-MM-yyyy");
                    }
                    String kq = from + " -> " + to;
                    while (rs.next()) {
                        BaocaoPro bc = new BaocaoPro();
                        bc.setNgay(kq);
                        bc.setDichvu(rs.getString(1));
                        bc.setSLDangKi(Integer.parseInt(rs.getString(2)));
                        bc.setSLHuy(Integer.parseInt(rs.getString(3)));
                        list.add(bc);
                    }
                } else {
                    while (rs.next()) {
                        BaocaoPro bc = new BaocaoPro();
                        bc.setNgay(rs.getString(1));
                        bc.setDichvu(rs.getString(2));
                        bc.setSLDangKi(Integer.parseInt(rs.getString(3)));
                        bc.setSLHuy(Integer.parseInt(rs.getString(4)));
                        list.add(bc);
                    }
                }
                int i = list.size();
                if (i > 0) {
                    FacesContext.getCurrentInstance().addMessage(null, new FacesMessage(i + " kết quả được thống kê!"));
                } else {
                    FacesContext.getCurrentInstance().addMessage(null, new FacesMessage("Không kết quả nào được thống kê!"));
                }
                return list;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            Database.closeObject(rs);
            Database.closeObject(st);
            Database.closeObject(cn);
        }
        return null;
    }
    //tim
    public String thongke() {
        items = searchByCondition();
        lazyDataModel = new LazyAbstractDataModel<BaocaoPro>(items);
        return "";
    }
}
